package com.tmpt.dao.impl.templateDao;

import com.tmpt.beans.A1TemplateColumn;
import com.tmpt.beans.A1TemplateTable;
import com.tmpt.dao.impl.TemplateUtils;
import com.tmpt.dao.intf.ITemplateDao;
import com.tmpt.dto.Result;
import com.tmpt.service.impl.TemplateServiceUtils;
import com.tmpt.service.utils.CommonDbDao;
import com.tmpt.utils.DateTool;
import com.tmpt.utils.FenAndYuan;
import com.tmpt.utils.StringUtils;
import net.sf.json.JSONArray;
import org.apache.log4j.Logger;
import org.hibernate.*;
import org.hibernate.jdbc.Work;
import org.hibernate.transform.Transformers;
import org.json.JSONObject;
import org.springframework.beans.factory.annotation.Autowired;

import java.sql.*;
import java.util.*;
import java.util.Date;

/**
 * Created by wxy on 2016/12/11.
 */
public class TemplateParent4MySqlDao implements ITemplateDao {

    private Logger log = Logger.getLogger(this.getClass());

    SessionFactory sessionFactory;
    public void setSessionFactory(SessionFactory sessionFactory) {
        this.sessionFactory = sessionFactory;
    }

    @Autowired private TemplateUtils templateUtils;
    @Autowired private TemplateServiceUtils templateServiceUtils;



    private CommonDbDao commonDao = new CommonDbDao();

    private Session getCurrentSession(){
        return sessionFactory.getCurrentSession();
    }


    public Result templateSearch(A1TemplateTable table,Map<String,String[]> paramMap,String[] params) throws Exception{

        //取列相关特征
        Map<String,String> columnAliasMap = templateUtils.loadColumnTableAlias(table);
        Map<String,Map<String,String>> dbcolumnsProperty = templateUtils.loadDbcolumnsProperty(table, getCurrentSession());
        Map<String,String> columnTableMap = (Map<String,String>)templateUtils.queryRalationTableList(table).getResultMap().get("columnTableMap");
        Map<String,A1TemplateColumn> transferA1TemplateColumnMap = templateUtils.transferA1TemplateColumnMap(table);


        Result resultObj = new Result();
        StringBuffer sb = new StringBuffer();
        StringBuffer sb4report = new StringBuffer();
        Session session = getCurrentSession();
        session.doWork(new Work() {
            @Override
            public void execute(Connection connection) throws SQLException {

                int page = Integer.parseInt(paramMap.get("page")[0]);
                int rows = Integer.parseInt(paramMap.get("rows")[0]);
                String sortOrder = paramMap.get("sortOrder")[0];
                String sortColumn = paramMap.get("sortColumn")[0];
                String tableName = table.getTableName();
                String primaryKey = table.getPrimaryKey();

                //验证参数合法性，防止SQL注入
                if(!templateUtils.check(tableName,sortColumn,sortOrder,connection)){
                    log.info("参数非法："+tableName+";"+sortColumn+";"+sortOrder);
                    throw new SQLException("参数非法："+tableName+";"+sortColumn+";"+sortOrder);
                }

                //查询历史数据
                String _historyId = null;
                if(paramMap.get("_historyId")!=null){
                    _historyId = paramMap.get("_historyId")[0];
                }

                //查询条件生成
                List<Object> paramList = new ArrayList<Object>();
                Map<String,String> conditionMap = new HashMap<String,String>();
                String conditionStr = templateUtils.loadConditions(paramMap, conditionMap, null,paramList,dbcolumnsProperty,columnTableMap,
                        transferA1TemplateColumnMap,table,params);

                //查询历史数据
                if(_historyId!=null){
                    conditionStr = "id="+_historyId;
                }

                Map<String,Object> resultMap = new HashMap<String, Object>();

                int mode = table.getMode();
                //查询历史数据
                if(_historyId!=null){
                    tableName = tableName+"_history";
                    primaryKey = "createDate";
                    boolean hasTable = false;
                    try {
                        hasTable = commonDao.hasTable(connection, tableName);
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                    if(!hasTable){
                        resultObj.fault("表名不存在");
                        return;
                    }
                }
                if(StringUtils.isNull(sortColumn)){
                    sortColumn = primaryKey;
                }

                List list = new ArrayList();

                //按表名拼接
                List<A1TemplateColumn> columnList = table.getA1TemplateColumnList();
                //将dataResoure=5，数据字典中数据，加载到column的option中
                templateUtils.loadColumnOptionList(columnList);

                //取“列”列表
                StringBuilder sbColumn = new StringBuilder();
                StringBuilder sbColumnSum = new StringBuilder();
                templateUtils.loadColumnString(sbColumn, sbColumnSum, columnList,columnAliasMap,dbcolumnsProperty,columnTableMap);
                String sbColumnString = sbColumn.toString();
                String sbColumnSumString = sbColumnSum.toString();

				/*
				SELECT * FROM table1 limit page,rows
				*/
                sb.setLength(0);
                sb.append("select ").append(sbColumnString);
                sb.append(" from ").append(tableName);
                if(!StringUtils.isNull(table.getTableAliasName())) {
                    sb.append(" ").append(table.getTableAliasName());
                }
                //sb.append(" where 1=1 ");
                if(conditionMap.keySet().size()>0){
                    sb.append(" where ");
                    sb.append(conditionStr);
                }
                /*//历史数据查询
                if(_historyId !=null){
                    sb.append(" and ");
                    sb.append(conditionStr);
                }*/
                sb.append(" order by ").append(sortColumn).append(" ").append(sortOrder);
                sb.append(" limit ").append(rows*page).append(" , ").append(rows);
                String sqlTmp = sb.toString();
                log.info("-sql:" + sqlTmp);
                List<Object> paramQueryList = new ArrayList<Object>();
                paramQueryList.addAll(paramList);
                list = queryListBySearch(sqlTmp,paramQueryList,dbcolumnsProperty,table);

                //生成报表用的sql
                sb4report.setLength(0);
                sb4report.append(" select ").append(sbColumnString);
                sb4report.append(" from ").append(tableName);
                if(conditionMap.keySet().size()>0){
                    sb4report.append(" where ");
                    sb4report.append(conditionStr);
                }
                String reportString = sb4report.toString();

                //合计
                sb.setLength(0);
                sb.append(" select count(*) from ").append(tableName);
                if(conditionMap.keySet().size()>0){
                    sb.append(" where ");
                    sb.append(conditionStr);
                }
                int count = queryCountBySearch(sb.toString(),paramList);

                //SUM
                //历史数据不用合计了
                Map<String,String> mapSum = null;
                if(_historyId==null){
                    if(sbColumnSumString.length()>3){
                        sb.setLength(0);
                        sb.append(" select ");
                        sb.append(sbColumnSumString);
                        sb.append(" from ").append(tableName);
                        if(conditionMap.keySet().size()>0){
                            sb.append(" where ");
                            sb.append(conditionStr);
                        }
                        Object o = queryListBySearch(sb.toString(),paramList,dbcolumnsProperty,table).get(0);
                        mapSum = (Map<String,String>)o;
                    }
                }

                resultMap.put("total", count);
                resultMap.put("rows", list);
                //历史数据不用合计了，上面控制了
                if(mapSum!=null)resultMap.put("footer", mapSum);
                //正常数据，非历史数据查询
                if(_historyId ==null){
                    resultMap.put("reportString", reportString);
                }
                resultObj.setResultMap(resultMap);
                resultObj.success();


            }
        });


        return resultObj;
    }


    /**
     * 按照tableName字段拼接sql,
     * select * from [T1 t1 left join T2 t2 on t1.t2_id=t2.id] where ....
     * @param table
     * @param paramMap
     * @return
     * @throws Exception
     */
    @Override
    public Result templateSearchByTableNameSql(A1TemplateTable table,Map<String,String[]> paramMap,String[] params) throws Exception{


        StringBuffer sb = new StringBuffer();
        StringBuffer sb4report = new StringBuffer();
        Result resultObj = new Result();


        Session session = getCurrentSession();
        session.doWork(new Work() {
            @Override
            public void execute(Connection connection) throws SQLException {

                String keyRandom = paramMap.get("_keyRandom")[0];
                int page = Integer.parseInt(paramMap.get("page")[0]);
                int rows = Integer.parseInt(paramMap.get("rows")[0]);
                String sortOrder = paramMap.get("sortOrder")[0];
                String sortColumn = paramMap.get("sortColumn")[0];
                String tableName = table.getTableName();
                String tableNameSql = table.getTableNameSql();
                //验证参数合法性，防止SQL注入
                if(!templateUtils.check(tableName,sortColumn,sortOrder,connection)){
                    log.info("参数非法："+tableName+";"+sortColumn+";"+sortOrder);
                    throw new SQLException("参数非法："+tableName+";"+sortColumn+";"+sortOrder);
                }

                String tableNameHistorySql = table.getTableNameHistorySql();
                String tableAliasName = table.getTableAliasName();
                String primaryKey = tableAliasName+"."+table.getPrimaryKey();
                if(StringUtils.isNull(sortColumn)){
                    sortColumn = table.getPrimaryKey();
                }
                sortColumn = tableAliasName+"."+sortColumn;


                //查询历史数据
                String _historyId = null;
                if(paramMap.get("_historyId")!=null){
                    _historyId = paramMap.get("_historyId")[0];
                }

                //找到所有查询条件字段的别名
                Map<String,String> columnAliasMap = templateUtils.loadColumnTableAlias(table);
                //找到所有字段的数据类型
                Map<String,Map<String,String>> dbcolumnsProperty = templateUtils.loadDbcolumnsProperty(table, getCurrentSession());
                Map<String,String> columnTableMap = (Map<String,String>)templateUtils.queryRalationTableList(table).getResultMap().get("columnTableMap");
                Map<String,A1TemplateColumn> transferA1TemplateColumnMap = templateUtils.transferA1TemplateColumnMap(table);

                //查询条件生成
                List<Object> paramList = new ArrayList<Object>();
                Map<String,String> conditionMap = new HashMap<String,String>();
                String conditionStr = templateUtils.loadConditions(paramMap, conditionMap,columnAliasMap,paramList,dbcolumnsProperty,
                        columnTableMap,transferA1TemplateColumnMap,table,params);


                //查询历史数据
                if(_historyId!=null){
                    conditionStr = tableAliasName+".id="+_historyId;
                }

                Map<String,Object> resultMap = new HashMap<String, Object>();

                //查询历史数据
                if(_historyId!=null){
                    tableName = tableName+"_history";
                    tableNameSql = tableNameHistorySql;
                    primaryKey = tableAliasName+".hid";
                    boolean hasTable = false;
                    try {
                        hasTable = commonDao.hasTable(connection, tableName);
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                    if(!hasTable){
                        resultObj.fault("表名不存在");
                        return;
                    }
                }

                List list = new ArrayList();;

                //按表名拼接
                List<A1TemplateColumn> columnList = table.getA1TemplateColumnList();

                //将dataResoure=5，数据字典中数据，加载到column的option中
                templateUtils.loadColumnOptionList(columnList);

                //取“列”列表
                StringBuilder sbColumn = new StringBuilder();
                StringBuilder sbColumnSum = new StringBuilder();
                templateUtils.loadColumnString(sbColumn, sbColumnSum, columnList, columnAliasMap, dbcolumnsProperty,columnTableMap);
                String sbColumnString = sbColumn.toString();
                String sbColumnSumString = sbColumnSum.toString();

				/*
				SELECT * FROM table1 limit page,rows
				*/
                sb.setLength(0);
                sb.append("select ").append(sbColumnString);
                sb.append(" from ").append(tableNameSql);
                //sb.append(" where 1=1 ");
                if(conditionMap.keySet().size()>0){
                    //sb.append(" and ");
                    sb.append(" where ");
                    sb.append(conditionStr);
                }
                /*//历史数据查询
                if(_historyId !=null){
                    sb.append(" and ");
                    sb.append(conditionStr);
                }*/
                sb.append(" order by ").append(sortColumn).append(" ").append(sortOrder);
                sb.append(" limit ").append(rows*page).append(" , ").append(rows);
                String sqlTmp = sb.toString();
                List<Object> paramQueryList = new ArrayList<Object>();
                paramQueryList.addAll(paramList);
                list = queryListBySearch(sqlTmp,paramQueryList,dbcolumnsProperty,table);

                //生成报表用的sql
                sb4report.setLength(0);
                sb4report.append(" select ").append(sbColumnString);
                sb4report.append(" from ").append(tableNameSql);
                if(conditionMap.keySet().size()>0){
                    sb4report.append(" where ");
                    sb4report.append(conditionStr);
                }
                String reportString = sb4report.toString();

                //合计
                sb.setLength(0);
                sb.append(" select count(*) from ").append(tableNameSql);
                if(conditionMap.keySet().size()>0){
                    sb.append(" where ");
                    sb.append(conditionStr);
                }
                int count = queryCountBySearch(sb.toString(),paramList);

                //SUM
                //历史数据不用合计了
                Map<String,String> mapSum = null;
                if(_historyId==null){
                    if(sbColumnSumString.length()>3){
                        sb.setLength(0);
                        sb.append(" select ");
                        sb.append(sbColumnSumString);
                        sb.append(" from ").append(tableNameSql);
                        if(conditionMap.keySet().size()>0){
                            sb.append(" where ");
                            sb.append(conditionStr);
                        }
                        Object o = queryListBySearch(sqlTmp,paramList,dbcolumnsProperty,table).get(0);
                        mapSum = (Map<String,String>)o;
                    }
                }

                resultMap.put("total", count);
                resultMap.put("rows", list);
                //历史数据不用合计了，上面控制了
                if(mapSum!=null)resultMap.put("footer", mapSum);
                //正常数据，非历史数据查询
                if(_historyId ==null){
                    resultMap.put("reportString", reportString);
                }

                resultObj.setResultMap(resultMap);
                resultObj.success();

            }
        });



        return resultObj;
    }

    @Override
    public Result templateSearchBySql(A1TemplateTable table,Map<String,String[]> paramMap) throws Exception{

        StringBuffer sb = new StringBuffer();
        StringBuffer sb4report = new StringBuffer();
        Result resultObj = new Result();

        Session session = getCurrentSession();
        session.doWork(new Work() {
            @Override
            public void execute(Connection connection) throws SQLException {
                String sortOrder = paramMap.get("sortOrder")[0];
                String sortColumn = paramMap.get("sortColumn")[0];
                String tableName = table.getTableName();
                //验证参数合法性，防止SQL注入
                if(!templateUtils.check(tableName,sortColumn,sortOrder,connection)){
                    log.info("参数非法："+tableName+";"+sortColumn+";"+sortOrder);
                    throw new SQLException("参数非法："+tableName+";"+sortColumn+";"+sortOrder);
                }
            }
        });

        int page = Integer.parseInt(paramMap.get("page")[0]);
        int rows = Integer.parseInt(paramMap.get("rows")[0]);
        String sortOrder = paramMap.get("sortOrder")[0];
        String sortColumn = paramMap.get("sortColumn")[0];
        //查询历史数据
        String _historyId = null;
        if(paramMap.get("_historyId")!=null){
            _historyId = paramMap.get("_historyId")[0];
        }

        String sqlPage = table.getSqlPage();
        String sqlCount = table.getSqlCount();
        String sqlPageHistory = table.getSqlPageHistory();
        String sqlCountHistory = table.getSqlCountHistory();
        String sqlSum = table.getSqlSum();
        String sqlReport = table.getSqlReport();
        String tableName = table.getTableName();
        String tableAliasName = table.getTableAliasName();
        String primaryKey = tableAliasName+"."+table.getPrimaryKey();


        if(StringUtils.isNull(sortColumn)){
            sortColumn = table.getPrimaryKey();
        }
        sortColumn = tableAliasName+"."+sortColumn;
        if(_historyId!=null){
            sqlPage = sqlPageHistory;
            sqlCount = sqlCountHistory;
        }


        //找到所有查询条件字段的别名
        Map<String,String> columnAliasMap = templateUtils.loadColumnTableAlias(table);
        //找到所有字段的数据类型 {"column_name":{"data_type":"int","length":"0"},...}
        Map<String,Map<String,String>> dbcolumnsProperty = templateUtils.loadDbcolumnsProperty(table, getCurrentSession());
        Map<String,String> columnTableMap = (Map<String,String>)templateUtils.queryRalationTableList(table).getResultMap().get("columnTableMap");
        Map<String,A1TemplateColumn> transferA1TemplateColumnMap = templateUtils.transferA1TemplateColumnMap(table);

        //查询条件生成
        List<Object> paramList = new ArrayList<Object>();
        Map<String,String> conditionMap = new HashMap<String,String>();
        String conditionStr = templateUtils.loadConditions(paramMap, conditionMap,columnAliasMap,paramList,dbcolumnsProperty,
                columnTableMap,transferA1TemplateColumnMap,table,null);
        //查询条件：查询历史数据
        if(_historyId!=null){
            conditionStr = tableAliasName+".id="+_historyId;
        }

        //Session session = super.getSessionFactory().getCurrentSession();
        Map<String,Object> resultMap = new HashMap<String, Object>();

        //按表名拼接
        List<A1TemplateColumn> columnList = table.getA1TemplateColumnList();

        //取“列”列表
        StringBuilder sbColumn = new StringBuilder();
        StringBuilder sbColumnSum = new StringBuilder();
        templateUtils.loadColumnString(sbColumn, sbColumnSum, columnList,columnAliasMap,dbcolumnsProperty,columnTableMap);
        String sbColumnString = sbColumn.toString();
        String sbColumnSumString = sbColumnSum.toString();

		/*
		String sqlPage =
				"SELECT TOP #rows #column " +
				"FROM A2Dept t1,A2Company t2 WHERE t1.a2Compnay_id=t2.id and " +
				"t1.id NOT IN (SELECT TOP #rows_page# t1.id FROM A2Dept t1,A2Company t2 WHERE t1.a2Company_id=t2.id #condition ORDER BY #sortColumn #sortOrder)  " +
				"#condition ORDER BY #sortColumn #sortOrder";
		*/
        List list = new ArrayList();
        sqlPage = sqlPage.replaceAll("#rows#", String.valueOf(rows));
        sqlPage = sqlPage.replaceAll("#column#", sbColumnString);
        sqlPage = sqlPage.replaceAll("#rows_page#", String.valueOf(rows*(page)));
        if(!StringUtils.isNull(conditionStr)){
            sqlPage = sqlPage.replaceAll("#condition#", "and "+conditionStr);
        }else{
            sqlPage = sqlPage.replaceAll("#condition#", "");
        }
        sqlPage = sqlPage.replaceAll("#sortColumn#", sortColumn);
        sqlPage = sqlPage.replaceAll("#sortOrder#", sortOrder);

        //list = queryReportBySearch(sqlPage);
        List<Object> paramQueryList = new ArrayList<Object>();
        paramQueryList.addAll(paramList);
        paramQueryList.addAll(paramList);
        list = queryListBySearch(sqlPage, paramQueryList);

        //合计
        //String sqlCount = "SELECT count(1) from A2Dept t1,A2Company t2 WHERE t1.a2Company_id=t2.id and t1.id and #condition#";
        if(!StringUtils.isNull(conditionStr)){
            sqlCount = sqlCount.replaceAll("#condition#", "and "+conditionStr);
        }else{
            sqlCount = sqlCount.replaceAll("#condition#", "");
        }
        //SQLQuery sqlQuery = session.createSQLQuery(sqlCount);
        //Query query = sqlQuery.setReadOnly(true);
        int count = queryCountBySearch(sqlCount, paramList);
        //int count = Integer.parseInt(query.uniqueResult().toString());

        //报表下载，历史记录不调用
        String reportString = null;
        if(!StringUtils.isNull(sqlReport) && _historyId==null){
            sqlReport = sqlReport.replaceAll("#column#", sbColumnString);
            if(!StringUtils.isNull(conditionStr)){
                sqlReport = sqlReport.replaceAll("#condition#", "and "+conditionStr);
            }else{
                sqlReport = sqlReport.replaceAll("#condition#", "");
            }
            sqlReport = sqlReport.replaceAll("#sortColumn#", sortColumn);
            sqlReport = sqlReport.replaceAll("#sortOrder#", sortOrder);
            reportString = sqlReport;
        }

        //SUM，历史记录不调用
        Map<String,String> mapSum = null;
        if(!StringUtils.isNull(sqlSum) && _historyId==null){
            if(!StringUtils.isNull(conditionStr)){
                sqlSum = sqlSum.replaceAll("#condition#", "and "+conditionStr);
            }else{
                sqlSum = sqlSum.replaceAll("#condition#", "");
            }
            Object o = queryListBySearch(sqlSum,paramList).get(0);
            mapSum = (Map<String,String>)o;
        }

        resultMap.put("total", count);
        resultMap.put("rows", list);
        //历史数据不用合计了，上面控制了
        if(mapSum!=null)resultMap.put("footer", mapSum);
        //正常数据，非历史数据查询
        if(reportString!=null)resultMap.put("reportString", reportString);

        resultObj.setResultMap(resultMap);

        return resultObj.success();
    }


    @Override
    public Result templateSearchByProcedure(A1TemplateTable table,Map<String,String[]> paramMap)  throws Exception{
        Result resultObj = new Result();

        Session session = getCurrentSession();
        session.doWork(new Work() {
            @Override
            public void execute(Connection connection) throws SQLException {

                //#page=1#rows=60#sortOrder=desc#sortColumn=id#createDate_Start=2016-10-27 21:13:43#status=1#createDate_End=2016-10-28 21:13:47
                StringBuilder sbParam = new StringBuilder();
                int page = Integer.parseInt(paramMap.get("page")[0]);
                int rows = Integer.parseInt(paramMap.get("rows")[0]);
                String sortOrder = paramMap.get("sortOrder")[0];
                String sortColumn = paramMap.get("sortColumn")[0];
                if (StringUtils.isNull(sortColumn)) {
                    sortColumn = table.getPrimaryKey();
                }
                String tableName = table.getTableName();

                //验证参数合法性，防止SQL注入
                if (!templateUtils.check(tableName, sortColumn, sortOrder, connection)) {
                    log.info("参数非法：" + tableName + ";" + sortColumn + ";" + sortOrder);
                    throw new SQLException("参数非法：" + tableName + ";" + sortColumn + ";" + sortOrder);
                }


                //拼接参数
                Iterator<String> it = paramMap.keySet().iterator();
                while (it.hasNext()) {
                    String key = it.next();
                    String value = paramMap.get(key)[0];
                    if (key.equals("_keyRandom") ||
                            key.equals("page") ||
                            key.equals("rows") ||
                            key.equals("sortOrder") ||
                            key.equals("sortColumn") ||
                            key.equals("_historyId")
                            ) {
                        continue;
                    }
                    sbParam.append("#").append(key).append("=").append(value);
                }
                String sbParamString = sbParam.toString();
                //查询历史数据
                String _historyId = null;
                if (paramMap.get("_historyId") != null) {
                    _historyId = paramMap.get("_historyId")[0];
                }

                List<Map<String, String>> list = new ArrayList<Map<String, String>>();
                Map<String, Object> resultMap = new HashMap<String, Object>();


                String sqlProcedure = table.getSqlProcedure();
                CallableStatement cstmt = null;
                ResultSet rs = null;
                ResultSet rs1 = null;
                String sqlProcedureString = "{call " + sqlProcedure + "(?,?,?,?,?,?)}";

                cstmt = connection.prepareCall(sqlProcedureString);
                cstmt.setInt(1, page);
                cstmt.setInt(2, rows);
                cstmt.setString(3, sortOrder);
                cstmt.setString(4, sortColumn);
                cstmt.setString(5, sbParamString);
                cstmt.registerOutParameter(6, Types.VARCHAR);
                cstmt.execute();
                rs = cstmt.getResultSet();
                while (rs.next()) {
                    ResultSetMetaData rsmd = rs.getMetaData();
                    int colCount = rsmd.getColumnCount();
                    HashMap<String, String> hash = new HashMap<String, String>();
                    for (int i = 1; i <= colCount; i++) {
                        Object tmp = rs.getObject(i);
                        if (tmp != null) {
                            hash.put(rsmd.getColumnName(i), String.valueOf(tmp));
                        } else {
                            hash.put(rsmd.getColumnName(i), "");
                        }
                    }
                    list.add(hash);
                }


                Map<String, String> mapSum = null;
                if (cstmt.getMoreResults()) {
                    rs1 = cstmt.getResultSet();
                    if (rs1.next()) {
                        ResultSetMetaData rsmd = rs1.getMetaData();
                        int colCount = rsmd.getColumnCount();
                        mapSum = new HashMap<String, String>();
                        for (int i = 1; i <= colCount; i++) {
                            Object tmp = rs1.getObject(i);
                            if (tmp != null) {
                                mapSum.put(rsmd.getColumnName(i), String.valueOf(tmp));
                            } else {
                                mapSum.put(rsmd.getColumnName(i), "");
                            }
                        }
                    }
                }

                cstmt.getMoreResults(Statement.KEEP_CURRENT_RESULT);//第二个结果--记录数
                String result = cstmt.getString(6);
                int count = Integer.parseInt(result);
                String reportString = null;

                resultMap.put("total", count);
                resultMap.put("rows", list);
                //历史数据不用合计了，上面控制了
                if (mapSum != null) resultMap.put("footer", mapSum);
                //正常数据，非历史数据查询
                if (reportString != null) resultMap.put("reportString", reportString);

                resultObj.setResultMap(resultMap);


            }
        });


        return resultObj.success();
    }

    @Override
    public List<Map<String, Object>> queryReportBySearch(String sqlstring) {
        Session session = getCurrentSession();
        SQLQuery sqlQuery = session.createSQLQuery(sqlstring);
        Query query = sqlQuery.setReadOnly(true);
        query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        return query.list();
    }

    @Override
    public List<Map<String, Object>> queryListBySearch(String sqlstring, List<Object> paramList){
        return queryListBySearch(sqlstring, paramList, null, null);
    }

    /**
     * 根据列类型，对返回的数据集加数据转换，如：datetime -> datetime_str
     * @param sqlstring
     * @param paramList
     * @param dbcolumnsProperty
     * @return
     */
    public List<Map<String, Object>> queryListBySearch(String sqlstring, List<Object> paramList, Map<String,Map<String,String>> dbcolumnsProperty,A1TemplateTable table) {
        Session session = getCurrentSession();
        SQLQuery sqlQuery = session.createSQLQuery(sqlstring);
        int i=0;
        if(paramList!=null){
            for(Object o:paramList){
                sqlQuery.setParameter(i++, o);
            }
        }
        Query query = sqlQuery.setReadOnly(true);
        query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List<Map<String, Object>> list = query.list();

        //对查询出的结果集做数据转换
        transferResultList(list,dbcolumnsProperty,table);

        return list;
    }

    /**
     * 对查询出的结果集做数据转换
     * @param list
     * @param dbcolumnsProperty
     * @param table
     * @return
     */
    private void transferResultList(List<Map<String, Object>> list,Map<String,Map<String,String>> dbcolumnsProperty,A1TemplateTable table){
        if(dbcolumnsProperty==null || table==null){return;}

        //查询列所属表名
        Result result1 = templateUtils.queryRalationTableList(table);
        Map<String,String> columnTableMap = (Map<String,String>)result1.getResultMap().get("columnTableMap");

        //取出每一列的data_tranfer
        Map<String,A1TemplateColumn> a1TemplateColumnMap = templateUtils.transferA1TemplateColumnMap(table);

        //存放转换后的数据列表
        List<Map<String,Object>> newList = new ArrayList<>();
        //for处理column map
        for (Map<String, Object> map : list) {
            //存放转换后的数据Map
            Map<String,Object> newMap = new HashMap<>();
            Set columnSet = map.keySet();
            Iterator columnIt = columnSet.iterator();
            while (columnIt.hasNext()) {
                //原数据
                String columnName = columnIt.next().toString();
                Object columnValue = map.get(columnName);
                newMap.put(columnName, columnValue);
                //取列的模板对象
                A1TemplateColumn a1TemplateColumn = a1TemplateColumnMap.get(columnName);
                //转换
                String dbPropertyMapKey = columnTableMap.get(columnName)+"_"+columnName;
                Map<String, String> dataPropertyMap = dbcolumnsProperty.get(dbPropertyMapKey);
                if (dataPropertyMap!=null ) {
                    String dataType = dataPropertyMap.get("data_type");
                    if (dataType != null) {
                        //日期类型转换,增加列如 createdate_Str
                        if (dataType.equalsIgnoreCase("datatime")
                                || dataType.equalsIgnoreCase("date")
                                || dataType.equalsIgnoreCase("timestamp")) {
                            //在做合计时，balancedate是‘合计：’，所有出错，catch处理一下
                            try {
                                String dateformat = "yyyy-MM-dd HH:mm:ss";
                                if(a1TemplateColumn!=null) {
                                    String dataTransfer = a1TemplateColumn.getDataTransfer();
                                    if("date".equalsIgnoreCase(dataTransfer)){
                                        dateformat = "yyyy-MM-dd";
                                    }
                                }
                                String columnValue_DateStr = DateTool.date2String((Date) columnValue, dateformat);
                                newMap.put(columnName, columnValue_DateStr);
                            }catch (Exception e){
                                newMap.put(columnName, columnValue);
                            }
                        }
                    }
                }

                //转换 data_transfer
                if(a1TemplateColumn!=null) {
                    String dataTransfer = a1TemplateColumn.getDataTransfer();
                    //金额转换
                    if ("fen2yuan".equalsIgnoreCase(dataTransfer)) {
                        String yuan = FenAndYuan.fen2yuanStr(columnValue);
                        newMap.put(columnName, yuan);
                    }
                }

                //数据库中存储的string是json类型，页面展示时会变成[Object,Object]
                try {
                    if(columnValue.toString().charAt(0)=='{'
                        || columnValue.toString().charAt(0)=='[') {
                        newMap.put(columnName, " "+columnValue);
                    }
                }catch (Exception e){}

                //xxx

            }
            newList.add(newMap);
        }
        //数据load到list里返回
        list.clear();
        list.addAll(newList);
    }


    @Override
    public int queryCountBySearch(String sqlstring, List<Object> paramList) {
        Session session = getCurrentSession();
        SQLQuery sqlQuery = session.createSQLQuery(sqlstring);

        int i=0;
        if(paramList!=null){
            for(Object o:paramList){
                sqlQuery.setParameter(i++, o);
            }
        }

        Query query = sqlQuery.setReadOnly(true);
        int count = Integer.parseInt(query.uniqueResult().toString());
        return count;
    }


    @Override
    public long addData(String tableName, List<Map<String,Object>> column_list) throws HibernateException, SQLException{

        Map<String,Object> dataMap = new HashMap<>();
        Session session = getCurrentSession();
        session.doWork(new Work() {
            @Override
            public void execute(Connection connection) throws SQLException {
                long id1 = commonDao.addData(tableName, column_list, connection);
                dataMap.put("id", id1);
            }
        });

        long id = Long.parseLong(dataMap.get("id").toString());
        return id;
    }

    @Override
    public long editData(String tableName, String id, List<Map<String,Object>> column_list)
            throws HibernateException, SQLException {
        Map<String,Object> dataMap = new HashMap<>();

        Session session = getCurrentSession();
        Work work = new Work() {
            @Override
            public void execute(Connection connection) throws SQLException {
                long id1 = commonDao.updateData(tableName, id, column_list, connection);
                dataMap.put("id", id1);
            }
        };
        session.doWork(work);

        long id1 = Long.parseLong(dataMap.get("id").toString());
        return id1;
    }


    /**
     * 取表列 List
     */
    @Override
    public List<Map<String, Object>> loadColumn(A1TemplateTable table){
        //"select name from paydb.dbo.syscolumns where id=(select id from paydb.dbo.sysobjects where name='FinanceOrderPaySum')";
        //Select Name FROM SysColumns Where id=Object_Id('FinanceOrderPaySum')
        StringBuilder sb = new StringBuilder();
        //sb.append("select column_name as name from information_schema.columns where table_name=? and table_schema='mytmpt'");//以后加上，防止重表
        sb.append("select column_name as name from information_schema.columns where table_name=?");
        String sqlTableName = sb.toString();
        List<Object> paramList = new ArrayList<>();
        paramList.add(table.getTableName());
        List<Map<String, Object>> list = queryListBySearch(sqlTableName, paramList);
        return list;
    }


    /**
     * 取表主键
     * @param table
     * @return
     */
    @Override
    public String loadTablePrimaryKey(A1TemplateTable table){
        //SELECT COLUMN_NAME as primaryKey FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='FinanceOrderPaySum'
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT COLUMN_NAME as primaryKey FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME=?");
        String sqlTableName = sb.toString();
        List<Object> paramList = new ArrayList<>();
        paramList.add(table.getTableName());
        List<Map<String, Object>> list = queryListBySearch(sqlTableName, paramList);
        String primaryKey = (String)list.get(0).get("primaryKey");
        return primaryKey;
    }


    @Override
    public Result executeProcedureJob(String procedure, Map<String,Object> paramMap){
        Result result = new Result();
        try{

            Session session = getCurrentSession();
            session.doWork(new Work() {
                @Override
                public void execute(Connection connection) throws SQLException {
                    CallableStatement cstmt = null;
                    String sqlProcedureString = "{call " + procedure + "(?,?)}";
                    cstmt = connection.prepareCall(sqlProcedureString);
                    cstmt.setString(1, paramMap.toString());
                    cstmt.registerOutParameter(2, Types.VARCHAR);
                    cstmt.execute();
                }
            });

            return result.success();
        }catch (Exception e) {
            e.printStackTrace();
            return result.fault(e.getLocalizedMessage());
        }



    }

    @Override
    public List<String> queryDB(String tableName,String columnName){

        Session session = getCurrentSession();
        //验证参数合法性，防止SQL注入
        if(!templateUtils.check(tableName,columnName,session)){
            //throw new SQLException("参数非法："+tableName+";"+columnName);
            log.info("参数非法："+tableName+";"+columnName);

            return null;
        }


        List<String> resultList = new ArrayList<String>();
        String sql = "select "+columnName+" as a from "+tableName+" where deleteFlag=0";
        List<Map<String,Object>> list = queryReportBySearch(sql);
        for(Map<String,Object> map : list){
            String s = String.valueOf(map.get("a"));
            resultList.add(s);
        }
        return resultList;
    }

    @Override
    public List<Map<String,Object>> queryDB(String tableName,String columnNameLabel,String columnNameValue){

        Session session = getCurrentSession();
        //验证参数合法性，防止SQL注入
        if(!templateUtils.check(tableName,columnNameLabel,session)){
            //throw new SQLException("参数非法："+tableName+";"+columnNameLabel);
            log.info("参数非法："+tableName+";"+columnNameLabel);
            return null;
        }
        if(!templateUtils.check(tableName,columnNameValue,session)){
            //throw new SQLException("参数非法："+tableName+";"+columnNameValue);
            log.info("参数非法："+tableName+";"+columnNameValue);
            return null;
        }


        List<String> resultList = new ArrayList<String>();
        StringBuilder sb = new StringBuilder();
        sb.append("select ");
        sb.append(columnNameLabel).append(" as label ,");
        sb.append(columnNameValue).append(" as value ");
        sb.append("from ").append(tableName).append(" ");
        sb.append("where deleteFlag=0");
        String sql = sb.toString();
        List<Map<String,Object>> list = queryReportBySearch(sql);
        return list;
    }

    @Override
    public List<Map<String,Object>> queryDB(String targetTableName, String targetColumns, String targetPrimaryKey, int targetTableId){

        Session session = getCurrentSession();
        //验证参数合法性，防止SQL注入
        if(!templateUtils.check4multiColumns(targetTableName, targetColumns, session)){
            //throw new SQLException("参数非法："+targetTableName+";"+targetColumns);
            log.info("参数非法："+targetTableName+";"+targetColumns);
            return null;
        }

        List<String> resultList = new ArrayList<String>();
        StringBuilder sb = new StringBuilder();
        sb.append("select ");
        sb.append(targetColumns).append(" ");
        sb.append("from ").append(targetTableName).append(" ");
        sb.append("where ").append(targetPrimaryKey).append("=?");
        String sql = sb.toString();

        List<Object> paramList = new ArrayList<>();
        paramList.add(targetTableId);
        List<Map<String,Object>> list = queryListBySearch(sql, paramList);
        return list;
    }

    @Override
    public List<Map<String,Object>> queryDBLast(String targetTableName, String targetColumns, String targetPrimaryKey){

        Session session = getCurrentSession();
        //验证参数合法性，防止SQL注入
        if(!templateUtils.check4multiColumns(targetTableName, targetColumns, session)){
            //throw new SQLException("参数非法："+targetTableName+";"+targetColumns);
            log.info("参数非法："+targetTableName+";"+targetColumns);
            return null;
        }
        if(!templateUtils.check(targetTableName, targetPrimaryKey, session)){
            //throw new SQLException("参数非法："+targetTableName+";"+targetPrimaryKey);
            log.info("参数非法："+targetTableName+";"+targetPrimaryKey);
            return null;
        }

        StringBuilder sb = new StringBuilder();
        sb.append("select top 1 ");
        sb.append(targetColumns).append(" ");
        sb.append("from ").append(targetTableName).append(" ");
        sb.append("order by ").append(targetPrimaryKey).append(" desc");
        String sql = sb.toString();
        List<Map<String,Object>> list = queryReportBySearch(sql);
        return list;
    }

    public boolean createTableNotExist(String tableName) throws HibernateException, SQLException{

        Session session = getCurrentSession();
        session.doWork(new Work() {
            @Override
            public void execute(Connection connection) throws SQLException {
                boolean hasTable = commonDao.hasTable(connection, tableName);
                //表不存在，创建一张表
                if(!hasTable){
                    if(!templateUtils.checkTableNameLegality(tableName)){
                        throw new SQLException("参数非法："+tableName);
                    }

                    StringBuilder sqlCreateSB = new StringBuilder();
                    sqlCreateSB.append("create table ").append(tableName).append("(");
                    sqlCreateSB.append("id bigint identity(1,1) primary key,");
                    sqlCreateSB.append("createDate datetime,");
                    sqlCreateSB.append("createMan varchar(255),");
                    sqlCreateSB.append("modifyDate datetime,");
                    sqlCreateSB.append("modifyMan varchar(255),");
                    sqlCreateSB.append("deleteFlag int");
                    sqlCreateSB.append(")");
                    String sqlCreate = sqlCreateSB.toString();
                    int count = commonDao.updateData(sqlCreate, null, connection);
                }
            }
        });

        return true;
    }


    @Override
    public boolean alterColumnNotExist(String tableName,String columnName,String column_dataType, String column_isNull) throws HibernateException, SQLException{

        Session session = getCurrentSession();
        session.doWork(new Work() {
            @Override
            public void execute(Connection connection) throws SQLException {

                boolean hasColumn = commonDao.hasColumn(connection, tableName, columnName);
                //表不存在，创建一张表
                if(!hasColumn){
                    if(!templateUtils.checkColumnNameLegality(columnName)){
                        throw new SQLException("参数非法："+columnName);
                    }
                    StringBuilder sqlCreateSB = new StringBuilder();
                    sqlCreateSB.append("alter table ").append(tableName).append(" ");
                    sqlCreateSB.append("add ").append(columnName).append(" ").append(column_dataType).append(" ").append(column_isNull);
                    String sqlCreate = sqlCreateSB.toString();
                    int count = commonDao.updateData(sqlCreate, null, connection);
                }
            }
        });

        return true;
    }

    /**
     * 取列属性相关数据
     * @param table
     * @return
     * @throws Exception
     */
    @Override
    public Result queryColumnAliasAndProperty(A1TemplateTable table) throws Exception{

        Result resultObj = new Result();
        //找到所有查询条件字段的别名
        Map<String,String> columnAliasMap = templateUtils.loadColumnTableAlias(table);
        resultObj.getResultMap().put("columnAliasMap",columnAliasMap);

        //找到所有字段的数据类型 {"column_name":{"data_type":"int","length":"0"},...}
        Map<String,Map<String,String>> dbcolumnsProperty = templateUtils.loadDbcolumnsProperty(table, getCurrentSession());
        resultObj.getResultMap().put("dbcolumnsProperty", dbcolumnsProperty);

        return resultObj;

    }

    /*@Override
    public Result genSql4templateSearchByTableName(A1TemplateTable table,Map<String,String[]> paramMap) throws Exception{
        *//*TemplateParent4PGDaoUtils templateParent4PGDaoUtils = new TemplateParent4PGDaoUtils();
        return templateParent4PGDaoUtils.getSql4templateSearchByTableName(table, templateUtils);*//*

        Result resultObj = new Result();
        //找到所有查询条件字段的别名
        Map<String,String> columnAliasMap = templateUtils.loadColumnTableAlias(table);
        resultObj.getResultMap().put("columnAliasMap",columnAliasMap);

        Session session = getCurrentSession();
        session.doWork(new Work() {
            @Override
            public void execute(Connection connection) throws SQLException {
                Map<String,Map<String,String>> dbcolumnsProperty = commonDao.queryColumnsProperty(connection,table.getTableName());
                resultObj.getResultMap().put("dbcolumnsProperty",dbcolumnsProperty);
            }
        });


        return resultObj;
    }*/


}
